﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;

namespace DBUtil.FastData
{
    public class Api
    {
        public Api(DBAccess db)
        {
            Db = db;
        }

        public DBAccess Db { get; }

        #region 删除数据
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="commands">删除数据命令</param>
        /// <returns></returns>
        public CommandResult DeleteData(List<DeleteCommand> commands)
        {
            Dictionary<string, object> res = new Dictionary<string, object>();
            try
            {
                Db.RunInTransaction(() =>
                {
                    foreach (var command in commands)
                    {
                        //关键字名称
                        string key = command.Tag;
                        string tbl = command.TableName;
                        //过滤条件
                        (string filterSql, DbParameter[] paras) = CreateFilter(command);
                        if (string.IsNullOrWhiteSpace(filterSql))
                        {
                            throw new Exception("删除的条件不能为空!");
                        }
                        int count = Db.ExecuteSql($"delete from {tbl} {filterSql}", paras);
                        res.Add(key, new
                        {
                            successRes = true,
                            returnRes = "删除成功!",
                            countRes = count
                        });
                    }
                });
                return new CommandResult()
                {
                    Success = true,
                    Data = res
                };
            }
            catch (Exception ex)
            {
                return new CommandResult()
                {
                    Success = false,
                    Message = ex?.Message
                };
            }
        }
        #endregion

        #region 查询数据
        /// <summary>
        /// 通用查询接口
        /// </summary>
        /// <param name="commands">查询命令</param>
        /// <returns></returns>
        public CommandResult QueryData(params QueryCommand[] commands)
        {
            var res = new CommandResult();
            foreach (var command in commands)
            {
                //关键字名称
                string key = command.Tag;
                string tbl = command.TableName;
                try
                {
                    string sql = "";
                    //过滤条件
                    (string filterSql, IDataParameter[] paras) = CreateFilter(command);
                    //排序字段
                    string orderSql = "";
                    if (command.Orders != null && command.Orders.Count > 0)
                    {
                        foreach (var order in command.Orders)
                        {
                            string name = order.Name;
                            string type = order.IsDesc ? "desc" : "asc";
                            if (orderSql.Length > 0)
                            {
                                orderSql += string.Format(",{0} {1}", name, type);
                            }
                            else
                            {
                                orderSql += string.Format(" order by {0} {1}", name, type);
                            }
                        }
                    }
                    //请求字段
                    string colSql = "*";
                    Dictionary<string, string> formats = new Dictionary<string, string>();
                    var _cols = command.Columns;
                    if (_cols != null && _cols.Count > 0)
                    {
                        foreach (var col in _cols)
                        {
                            var name = DealSqlFormat(col);
                            if (colSql == "*")
                            {
                                colSql = name;
                            }
                            else
                            {
                                colSql += "," + name;
                            }
                        }
                    }
                    var count = 0;
                    if (command.PageSize > 0)
                    {
                        //分页对象
                        count = Db.SelectScalar<int>($"select count(1) from {tbl} {filterSql}");
                        int pageSize = command.PageSize;
                        int pageIndex = command.PageIndex;
                        sql = Db.GetSqlForPageSize($"select {colSql} from {tbl} {filterSql}", orderSql, pageSize, pageIndex);
                    }
                    else
                    {
                        sql = $"select {colSql} from {tbl} {filterSql} {orderSql}";
                    }
                    var dics = Db.SelectDictionaryList(sql);
                    count = Math.Max(count, dics.Count);
                    //处理Format
                    if (_cols != null && _cols.Count > 0)
                    {
                        foreach (var dic in dics)
                        {
                            DealDisplayFormat(_cols, dic);
                        }
                    }
                    res.Data.Add(key, new
                    {
                        successRes = true,
                        returnRes = dics,
                        countRes = count
                    });
                    res.Success = true;
                    return res;
                }
                catch (Exception ex)
                {
                    res.Data.Add(key, new
                    {
                        successRes = false,
                        returnRes = ex?.Message
                    });
                }
            }
            return res;
        }
        #endregion

        #region 拼接过滤sql
        /// <summary>
        /// 生成过滤条件sql
        /// </summary>
        /// <param name="filterCommand">命令</param>
        /// <returns></returns>
        private (string filterSql, DbParameter[] paras) CreateFilter(FilterCommand filterCommand)
        {
            var filterModels = filterCommand.Filters;
            var filterPrefix = filterCommand.Tag + "_";
            string sql = " where 1=1";
            List<DbParameter> paras = new List<DbParameter>();
            foreach (var filterModel in filterModels)
            {
                var name = filterModel.Name;
                var value = filterModel.Value;
                var type = filterModel.OperateType;
                var paraname = filterPrefix + name;
                var useCommonPara = true;
                object[] arr = null;
                switch (type)
                {
                    case EnumOperateType.Equal:
                        sql += $" and {name} = {Db.ParaPrefix}{paraname}";
                        break;
                    case EnumOperateType.Greater:
                        sql += $" and {name} > {Db.ParaPrefix}{paraname}";
                        break;
                    case EnumOperateType.GreaterOrEqual:
                        sql += $" and {name} >= {Db.ParaPrefix}{paraname}";
                        break;
                    case EnumOperateType.Less:
                        sql += $" and {name} < {Db.ParaPrefix}{paraname}";
                        break;
                    case EnumOperateType.LessOrEqual:
                        sql += $" and {name} <= {Db.ParaPrefix}{paraname}";
                        break;
                    case EnumOperateType.Like:
                        sql += $" and {name} like {Db.ParaPrefix}{paraname}";
                        useCommonPara = false;
                        paras.Add(Db.CreatePara(paraname, $"%{value}%"));
                        break;
                    case EnumOperateType.Like_Before:
                        sql += $" and {name} like {Db.ParaPrefix}{paraname}";
                        useCommonPara = false;
                        paras.Add(Db.CreatePara(paraname, $"{value}%"));
                        break;
                    case EnumOperateType.Like_After:
                        sql += $" and {name} like {Db.ParaPrefix}{paraname}";
                        useCommonPara = false;
                        paras.Add(Db.CreatePara(paraname, $"%{value}"));
                        break;
                    case EnumOperateType.NotEqual:
                        sql += $" and {name} <> {Db.ParaPrefix}{paraname}";
                        break;
                    case EnumOperateType.NotNull:
                        sql += $" and {name} is not null";
                        useCommonPara = false;
                        break;
                    case EnumOperateType.IsNull:
                        sql += $" and {name} is null";
                        useCommonPara = false;
                        break;
                    case EnumOperateType.IsNullOrEmpty:
                        sql += $" and ({name} is null or {name} = '')";
                        useCommonPara = false;
                        break;
                    case EnumOperateType.NotNullOrEmpty:
                        sql += $" and {name} is not null and {name} <> ''";
                        useCommonPara = false;
                        break;
                    case EnumOperateType.Between:
                        arr = value as object[];
                        sql += $" and {name} between {Db.ParaPrefix}{paraname}_1 and {Db.ParaPrefix}{paraname}_2";
                        useCommonPara = false;
                        paras.Add(Db.CreatePara($"{paraname}_1", arr[0]));
                        paras.Add(Db.CreatePara($"{paraname}_2", arr[1]));
                        break;
                    case EnumOperateType.In:
                        arr = value as object[];
                        if (arr.Length == 0) break;
                        sql += $" and {name} in (";
                        var arr2 = new List<string>();
                        for (int i = 0; i < arr.Length; i++)
                        {
                            arr2.Add($"{Db.ParaPrefix}{filterPrefix}{paraname}_{i + 1}");
                            paras.Add(Db.CreatePara("{filterPrefix}{paraname}_{i + 1}", arr[i]));
                        }
                        sql += string.Join(",", arr2);
                        sql += ")";
                        useCommonPara = false;
                        break;
                    case EnumOperateType.None:
                    default:
                        break;
                }
                if (useCommonPara) paras.Add(Db.CreatePara(paraname, value));
            }
            return (sql, paras.ToArray());
        }
        #endregion

        #region 处理Sql格式
        /// <summary>
        /// 处理Sql格式
        /// </summary>
        /// <param name="col"></param>
        /// <returns></returns>
        public string DealSqlFormat(ColumnRequest col)
        {
            var name = col.Name;
            switch (col.SqlFormat)
            {
                case EnumDataSqlFormat.Wkt:
                    return $"{name}.STAsText() as {name}";
                case EnumDataSqlFormat.Count:
                    return $"count(1) as {name}";
                case EnumDataSqlFormat.Avg:
                    return $"avg({name}) as {name}";
                case EnumDataSqlFormat.Sum:
                    return $"sum({name}) as {name}";
                case EnumDataSqlFormat.Max:
                    return $"max({name}) as {name}";
                case EnumDataSqlFormat.Min:
                    return $"min({name}) as {name}";
                default:
                    return name;
            }

        }
        #endregion

        #region 处理显示的格式
        /// <summary>
        /// 处理显示的格式
        /// </summary>
        /// <param name="columnRequests"></param>
        /// <param name="dic"></param>
        public void DealDisplayFormat(List<ColumnRequest> columnRequests, IDictionary<string, object> dic)
        {
            var cols = dic.Keys.ToList();
            foreach (var item in cols)
            {
                var req = columnRequests.FirstOrDefault(c => c.Name == item);
                if (req != null)
                {
                    switch (req.DisplayFormat)
                    {
                        case EnumDataDisplayFormat.DateTimeLong:
                            if (DateTime.TryParse(dic[item].ToString(), out DateTime d))
                            {
                                dic[item] = d.ToString("yyyy-MM-dd HH:mm:ss.fff");
                            }
                            break;
                        case EnumDataDisplayFormat.DateTime:
                            if (DateTime.TryParse(dic[item].ToString(), out DateTime d2))
                            {
                                dic[item] = d2.ToString("yyyy-MM-dd HH:mm:ss");
                            }
                            break;
                        case EnumDataDisplayFormat.Date:
                            if (DateTime.TryParse(dic[item].ToString(), out DateTime d3))
                            {
                                dic[item] = d3.ToString("yyyy-MM-dd");
                            }
                            break;
                        case EnumDataDisplayFormat.Time:
                            if (DateTime.TryParse(dic[item].ToString(), out DateTime d4))
                            {
                                dic[item] = d4.ToString("HH:mm:ss");
                            }
                            break;
                        case EnumDataDisplayFormat.Number:
                            {
                                if (double.TryParse(dic[item].ToString(), out double d5))
                                {
                                    dic[item] = d5.ToString("0");
                                }
                                break;
                            }
                        case EnumDataDisplayFormat.Number_1:
                            {
                                if (double.TryParse(dic[item].ToString(), out double d5))
                                {
                                    dic[item] = d5.ToString("0.0");
                                }
                                break;
                            }
                        case EnumDataDisplayFormat.Number_2:
                            {
                                if (double.TryParse(dic[item].ToString(), out double d5))
                                {
                                    dic[item] = d5.ToString("0.00");
                                }
                                break;
                            }
                        case EnumDataDisplayFormat.Number_3:
                            {
                                if (double.TryParse(dic[item].ToString(), out double d5))
                                {
                                    dic[item] = d5.ToString("0.000");
                                }
                                break;
                            }
                        case EnumDataDisplayFormat.Number_4:
                            {
                                if (double.TryParse(dic[item].ToString(), out double d5))
                                {
                                    dic[item] = d5.ToString("0.0000");
                                }
                                break;
                            }
                        case EnumDataDisplayFormat.None:
                        default:
                            break;
                    }
                }
            }
        }
        #endregion
    }

    /// <summary>
    /// 命令执行结果
    /// </summary>
    public class CommandResult
    {
        /// <summary>
        /// 是否成功
        /// </summary>
        public bool Success { get; set; }

        /// <summary>
        /// 错误描述
        /// </summary>
        public string Message { get; set; }

        /// <summary>
        /// 返回数据
        /// </summary>
        public Dictionary<string, object> Data { get; set; } = new Dictionary<string, object>();
    }

    /// <summary>
    /// 抽象命令
    /// </summary>
    public abstract class Command
    {
        /// <summary>
        /// 附带字符串数据
        /// </summary>
        public string Tag { get; set; }

        /// <summary>
        /// 操作的表名称
        /// </summary>
        public string TableName { get; set; }
    }

    /// <summary>
    /// 带有过滤条件的命令
    /// </summary>
    public abstract class FilterCommand : Command
    {
        /// <summary>
        /// 过滤条件
        /// </summary>
        public List<FilterModel> Filters { get; set; } = new List<FilterModel>();
    }


    /// <summary>
    /// 删除命令
    /// </summary>
    public class DeleteCommand : FilterCommand { }

    /// <summary>
    /// 过滤条件
    /// </summary>
    public class FilterModel
    {
        /// <summary>
        /// 列名称
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// 列值
        /// </summary>
        public object Value { get; set; }

        /// <summary>
        /// 过滤条件的操作类型
        /// </summary>
        public EnumOperateType OperateType { get; set; }
    }

    /// <summary>
    /// 运算符
    /// </summary>
    public enum EnumOperateType
    {
        /// <summary>
        /// 默认,不用处理
        /// </summary>
        None,

        /// <summary>
        /// 相等比较
        /// </summary>
        Equal,

        /// <summary>
        /// 大于
        /// </summary>
        Greater,

        /// <summary>
        /// 大于等于
        /// </summary>
        GreaterOrEqual,

        /// <summary>
        /// 小于
        /// </summary>
        Less,

        /// <summary>
        /// 小于等于
        /// </summary>
        LessOrEqual,

        /// <summary>
        /// 模糊匹配 like '%xxxx%'
        /// </summary>
        Like,

        /// <summary>
        /// 模糊匹配 like 'xxxx%'
        /// </summary>
        Like_Before,

        /// <summary>
        /// 模糊匹配 like '%xxxx'
        /// </summary>
        Like_After,

        /// <summary>
        /// 不等于
        /// </summary>
        NotEqual,

        /// <summary>
        /// 不为null
        /// </summary>
        NotNull,

        /// <summary>
        /// 为null
        /// </summary>
        IsNull,

        /// <summary>
        /// 为null或者为空字符串
        /// </summary>
        IsNullOrEmpty,

        /// <summary>
        /// 不为null,也不为空字符串
        /// </summary>
        NotNullOrEmpty,

        /// <summary>
        /// between...and...
        /// </summary>
        Between,

        /// <summary>
        /// in关键字
        /// </summary>
        In,
    }

    /// <summary>
    /// 数据显示格式，由程序直接处理
    /// </summary>
    public enum EnumDataDisplayFormat
    {
        /// <summary>
        /// 默认,不用处理
        /// </summary>
        None,

        /// <summary>
        /// 日期时间(含毫秒): yyyy-MM-dd HH:mm:ss.fff
        /// </summary>
        DateTimeLong,

        /// <summary>
        /// 日期时间: yyyy-MM-dd HH:mm:ss
        /// </summary>
        DateTime,

        /// <summary>
        /// 日期: yyyy-MM-dd
        /// </summary>
        Date,

        /// <summary>
        /// 时间: HH:mm:ss
        /// </summary>
        Time,

        /// <summary>
        /// 整形数据
        /// </summary>
        Number,

        /// <summary>
        /// 1位小数
        /// </summary>
        Number_1,

        /// <summary>
        /// 2位小数
        /// </summary>
        Number_2,

        /// <summary>
        /// 3位小数
        /// </summary>
        Number_3,

        /// <summary>
        /// 4位小数
        /// </summary>
        Number_4
    }

    /// <summary>
    /// Sql操作格式，拼接到sql中执行
    /// </summary>
    public enum EnumDataSqlFormat
    {
        /// <summary>
        /// 默认,不用处理
        /// </summary>
        None,

        /// <summary>
        /// 将空间数据转换为geojson格式: xxxx..STAsText() as xxxx
        /// </summary>
        Wkt,

        /// <summary>
        /// 计数: count(1) as xxxx
        /// </summary>
        Count,

        /// <summary>
        /// 平均值: avg(xxxx) as xxxx
        /// </summary>
        Avg,

        /// <summary>
        /// 合计值: sum(xxxx) as xxxx
        /// </summary>
        Sum,

        /// <summary>
        /// 最大值: max(xxxx) as xxxx
        /// </summary>
        Max,

        /// <summary>
        /// 最小值: min(xxxx) as xxxx
        /// </summary>
        Min
    }

    /// <summary>
    /// 查询命令
    /// </summary>
    public class QueryCommand : FilterCommand
    {
        /// <summary>
        /// 请求的列
        /// </summary>
        public List<ColumnRequest> Columns { get; set; }

        /// <summary>
        /// 排序请求列
        /// </summary>
        public List<OrderRequest> Orders { set; get; }

        /// <summary>
        /// 分页时当前的页码: 从1开始,默认为1
        /// </summary>
        public int PageIndex { get; set; } = 1;

        /// <summary>
        /// 分页时的页大小: 为0代表不分页,默认为0
        /// </summary>
        public int PageSize { get; set; }
    }

    /// <summary>
    /// 排序列说明
    /// </summary>
    public class OrderRequest
    {
        /// <summary>
        /// 排序的列名称
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// 是否反序: 默认是 asc
        /// </summary>
        public bool IsDesc { get; set; }
    }

    /// <summary>
    /// 请求列
    /// </summary>
    public class ColumnRequest
    {
        /// <summary>
        /// 列名称
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// 列格式: c#处理
        /// </summary>
        public EnumDataDisplayFormat DisplayFormat { get; set; }

        /// <summary>
        /// 列类型: 拼接到sql
        /// </summary>
        public EnumDataSqlFormat SqlFormat { get; set; }

        /// <summary>
        /// 其他过滤参数
        /// </summary>
        public object Parameter { get; set; }
    }
}
